<?php

namespace Home\Controller;
use core\lib\Exception;
use Home\Org\Util\Sqlserver;
use Org\Util\UploadFile;
/**
 * 售前 售后服务
 * @author dem
 *
 */
class OmsorderController extends HomeController {
    


    public function index()
    {
        $startTime=I('startTime') ? I('startTime') :date('Y-m-d', strtotime('-7 day'));
        $endTime=I('endTime') ? $endTime=I('endTime') : $endTime=date('Y-m-d',time());
        $this->assign('startTime',$startTime);
        $this->assign('endTime',$endTime);
        $this->display();
    }
    public function getOrderList()
    {
        set_time_limit(0);
        ini_set("memory_limit", "-1");
        import("@.ORG.Util.Sqlserver");
        //查询参数，A.DocDate 发货日期，a.ITEMCODE 商品编码，d.cmanager 平台负责人，c.cardname 平台名称，a.Pmanager 产品经理
        $pageIndex = I('pageIndex') +1 ;
        $pageSize = I('pageSize')?:50;
        $where=" where 1=1 ";
        $startTime=I('startTime') ? I('startTime') :date('Y-m-d', strtotime('-7 day'));
        $endTime=I('endTime') ? $endTime=I('endTime') : $endTime=date('Y-m-d',time());
        $itemCode=I('itemCode');
        $cmanager=I('cmanager');
        $cardname=I('cardname');
        $Pmanager=I('Pmanager');
        if($itemCode){

            $where.= " and a.ITEMCODE  like '%". trim($itemCode) . "%'";
        }

        if($cmanager){

            $where.= " and d.cmanager  like '%". trim($cmanager) . "%'";
        }

        if($cardname){
            $where.= " and c.cardname like '%". trim($cardname) . "%'";
        }

        if($Pmanager){
            $where.= " and a.Pmanager like '%". trim($Pmanager). "%'";
        }

        if($startTime)
        {
            $where .=" and A.DocDate >= '{$startTime}'";
        }

        if($endTime)
        {
            $where .=" and A.DocDate < '{$endTime}'";
        }
        $sqlser = new Sqlserver();
        $sqlCount="SELECT
COUNT(1) as total
FROM
	ax_reportx a
LEFT JOIN OITM b ON a.itemcode = b.itemcode
LEFT JOIN OCRD c ON a.cardcode = c.cardcode
LEFT JOIN AX_ChannelList d ON c.CardCode = d.CardCode".$where;
        //echo $sqlCount;exit;

        $res = $sqlser->query(iconv("utf-8","gb2312",$sqlCount));
        $count=$res[0]['total'];
        $pageStart=($pageIndex-1)*$pageSize+1;
        $pageEnd=$pageStart+$pageSize-1;
        $sql="SELECT * from (
SELECT
ROW_NUMBER() OVER (ORDER BY A.DocDate DESC) AS rowNumber,
    a.freetxt,
    b.u_brand,
	A.DocDate ,
	a.ITEMCODE ,
	B.itemname ,
	a.Quantity,
	a.price ,
	a.linetotal ,
	a.stockprice ,
	a.LineStockPrice ,
	a.CardCode ,
	c.cardname ,
	d.cmanager ,
	a.Pmanager ,
	a.whscode ,
	a.calctax ,
	a.tureexpcost ,
	a.bankcharge ,
	a.giftprice ,
	a.giftaddexpressfee ,
	a.addexpressfee 
FROM
	ax_reportx a
LEFT JOIN OITM b ON a.itemcode = b.itemcode
LEFT JOIN OCRD c ON a.cardcode = c.cardcode
LEFT JOIN AX_ChannelList d ON c.CardCode = d.CardCode ".$where." ) tt where tt.rowNumber between $pageStart And  $pageEnd ";
        //echo $sql;exit;
        $list= $sqlser->query(iconv("utf-8","gb2312",$sql));
        echo json_encode(array('total'=>$count,'data'=>$list));exit;
    }
    public function export()
    {
        set_time_limit(0);
        ini_set("memory_limit", "-1");
        import("@.ORG.Util.Sqlserver");
        //查询参数，A.DocDate 发货日期，a.ITEMCODE 商品编码，d.cmanager 平台负责人，c.cardname 平台名称，a.Pmanager 产品经理
        $where=" where 1=1 ";
        $startTime=I('startTime') ? I('startTime') :date('Y-m-d', strtotime('-7 day'));
        $endTime=I('endTime') ? $endTime=I('endTime') : $endTime=date('Y-m-d',time());
        $itemCode=I('itemCode');
        $cmanager=I('cmanager');
        $cardname=I('cardname');
        $Pmanager=I('Pmanager');
        if($itemCode){

            $where.= " and a.ITEMCODE  like '%". trim($itemCode) . "%'";
        }

        if($cmanager){

            $where.= " and d.cmanager  like '%". trim($cmanager) . "%'";
        }

        if($cardname){
            $where.= " and c.cardname like '%". trim($cardname) . "%'";
        }

        if($Pmanager){
            $where.= " and a.Pmanager like '%". trim($Pmanager). "%'";
        }

        if($startTime)
        {
            $where .=" and A.DocDate >= '{$startTime}'";
        }

        if($endTime)
        {
            $where .=" and A.DocDate < '{$endTime}'";
        }
        $sqlser = new Sqlserver();

        $sqlCount="SELECT
COUNT(1) as total
FROM
	ax_reportx a
LEFT JOIN OITM b ON a.itemcode = b.itemcode
LEFT JOIN OCRD c ON a.cardcode = c.cardcode
LEFT JOIN AX_ChannelList d ON c.CardCode = d.CardCode ".$where;

        $res = $sqlser->query(iconv("utf-8","gb2312",$sqlCount));
        $count=$res[0]['total'];
        if($count>50000) {
            $this->error('数据量过大。最多只能导出50000条记录！');
        }


        $sql="SELECT   a.freetxt,
    b.u_brand,A.DocDate,a.ITEMCODE,B.itemname,a.Quantity,a.price,a.linetotal,a.stockprice,a.LineStockPrice,a.CardCode,c.cardname,d.cmanager,a.Pmanager,a.calctax,a.tureexpcost,a.bankcharge,a.giftprice,a.giftaddexpressfee,a.addexpressfee,a.whscode
from ax_reportx a   left  join OITM b  on a.itemcode=b.itemcode  left  join  OCRD c on  a.cardcode=c.cardcode  left join 
 AX_ChannelList d on c.CardCode=d.CardCode ".$where;

        $data= $sqlser->query(iconv("utf-8","gb2312",$sql));
        Vendor('Excel.PHPExcel');
        \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;  # 单元格缓存为MemoryGZip
//        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
//        $cacheSettings = array( 'memoryCacheSize' => '512MB');
//        \PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings);
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0)

//            SELECT  A.DocDate 发货日期, a.ITEMCODE 商品编码,B.itemname 商品名称, a.Quantity 数量 ,a.price 单价,a.linetotal 总计, a.stockprice 库存单价,a.LineStockPrice 库存价值总计,
//a.CardCode 平台代码,c.cardname 平台名称,d.cmanager 平台负责人 ,a.Pmanager 产品经理,a.whscode 发货仓库,a.calctax 税,a.tureexpcost 运费,a.bankcharge 银行费用,a.giftprice 赠品成本,a.giftaddexpressfee 赠品运费,a.addexpressfee 物流附加
//from ax_reportx a   left  join OITM b  on a.itemcode=b.itemcode  left  join  OCRD c on  a.cardcode=c.cardcode  left join
// AX_ChannelList d on c.CardCode=d.CardCode  where a.docdate>='2018-08-01'  and  a.docdate<='2018-08-10'
            ->setCellValue('A1','订单号')
            ->setCellValue('B1','品牌')
            ->setCellValue('C1','发货日期')
            ->setCellValue('D1','商品编码')
            ->setCellValue('E1','商品名称')
            ->setCellValue('F1','数量')
            ->setCellValue('G1','单价')
            ->setCellValue('H1','总计')
            ->setCellValue('I1','库存单价')
            ->setCellValue('J1','库存价值总计')
            ->setCellValue('K1','平台代码')
            ->setCellValue('L1','平台名称')
            ->setCellValue('M1','平台负责人')
            ->setCellValue('N1','产品经理')
            ->setCellValue('O1','税')
            ->setCellValue('P1','运费')
            ->setCellValue('Q1','银行费用')
            ->setCellValue('R1','赠品成本')
            ->setCellValue('S1','赠品运费')
            ->setCellValue('T1','物流附加')
            ->setCellValue('U1','发货仓库')
        ;
        $i=2;                //定义一个i变量，目的是在循环输出数据是控制行数
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        foreach ($data as $key=>$v){
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A' . $i, $v['order_code'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue("A".$i,  $v['freetxt'])
                ->setCellValue("B".$i,  $v['u_brand'])
                ->setCellValue("C".$i,  $v['DocDate'])
                ->setCellValue("D".$i, $v['ITEMCODE'])
                ->setCellValue("E".$i, $v['itemname'])
                ->setCellValue("F".$i,  sprintf("%.2f",$v['Quantity']))
                ->setCellValue("G".$i, sprintf("%.2f",$v['price']))
                ->setCellValue("H".$i, sprintf("%.2f",$v['linetotal']))
                ->setCellValue("I".$i,  sprintf("%.2f",$v['stockprice']))
                ->setCellValue("J".$i, sprintf("%.2f",$v['LineStockPrice']))
                ->setCellValue("K".$i, $v['CardCode'])

                ->setCellValue("L".$i,  $v['cardname'])
                ->setCellValue("M".$i, $v['cmanager'])
                ->setCellValue("N".$i, $v['Pmanager'])
                ->setCellValue("O".$i, sprintf("%.2f",$v['calctax']))
                ->setCellValue("P".$i, sprintf("%.2f",$v['tureexpcost']))
                ->setCellValue("Q".$i,  sprintf("%.2f",$v['bankcharge']))
                ->setCellValue("R".$i, sprintf("%.2f",$v['giftprice']))
                ->setCellValue("S".$i, sprintf("%.2f",$v['giftaddexpressfee']))
                ->setCellValue("T".$i,  sprintf("%.2f",$v['addexpressfee']))
                ->setCellValue("U".$i,  $v['whscode']);
            $i++;
        }
        $objPHPExcel->getActiveSheet()->setTitle('中行365订单状态反馈表');
        $objPHPExcel->setActiveSheetIndex(0);
        $filename='boc365_pushStatus'. date('YmdHis',time());
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');

    }



}